﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;//引入acesss数据库
using System.Data;
using System.Data.SqlClient;
using System.Web.Security;
using Entity;
namespace Web.admin.students
{
    public partial class InsertAll : System.Web.UI.Page
    {
        //BLL.Students students = new BLL.Students();
        BLL.StudentService studentsService = new BLL.StudentService();
        BLL.ClassService classes = new BLL.ClassService();
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        /// <summary>
        /// 将excel表格用access数据库打开
        /// </summary>
        /// <param name="ExcelFileName">文件路径</param>
        /// <param name="ExcelSheet">工作表名称</param>
        /// <returns>返回excel中表格数据</returns>
        protected DataTable ExcelToDb(string ExcelFileName, string ExcelSheet)
        {
            //犹如sqlserver中的sqlconnection
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelFileName + ";" + "Extended Properties=Excel 5.0;";
            //犹如sqlstring，查询语句
            string strExcel = string.Format("select * from [{0}$]", ExcelSheet);
            DataTable dt = new DataTable();
            using (OleDbConnection con = new OleDbConnection(strCon))
            {
                OleDbDataAdapter odda = new OleDbDataAdapter(strExcel, con);
                odda.Fill(dt);
                return dt;
            }
        }
        /// <summary>
        /// 批量导入点击事件    
        /// </summary>
        /// <param name="sender">对象</param>
        /// <param name="e">事件</param>
        protected void btn_up_Click(object sender, EventArgs e)
        {
            BLL.MajorService majorservice = new BLL.MajorService();
            BLL.ClassService classervice = new BLL.ClassService();
            //判断是否选中文件
            if (ExcelUpLoad.HasFile)
            {
                //得到文件类型
                string fileClass = System.IO.Path.GetExtension(ExcelUpLoad.FileName).ToString();
                if (fileClass == ".xls" || fileClass == ".xlsx")
                {
                    string SavaPath = MapPath("../../Upload").ToString();
                    //如果不存在此路径，则创建
                    if (!System.IO.Directory.Exists(SavaPath))
                    {
                        System.IO.Directory.CreateDirectory(SavaPath);
                    }
                    string name = ExcelUpLoad.FileName;
                    DateTime dtime = DateTime.Now;
                    string newname = dtime.ToString("yyyyMMddHHmmssff") + fileClass;
                    //保存完整路径
                    SavaPath = SavaPath + "\\" + newname;
                    //保存文件
                    ExcelUpLoad.SaveAs(SavaPath);
                    DataTable dt = ExcelToDb(SavaPath, "Sheet1");
                    BoStudents studentModel = new BoStudents();
                    int count = 1;
                    //excel的格式要求，姓名，性别，学号，密码，学院，身份证号
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (!string.IsNullOrEmpty(dr["学号"].ToString()))
                        {
                            if (studentsService.GetV_studentByCode(dr["学号"].ToString()) != null)
                            {
                                Response.Write("<script>alert('第" + (count + 1) + "行出现错误,该学生已经录入)</script>");
                                return;
                            }
                            else
                                studentModel.STU_CODE = dr["学号"].ToString();
                        }
                        else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,学号不允许为空，请检查后再次导入')</script>");
                            return;
                        }
                        if (!string.IsNullOrEmpty(dr["姓名"].ToString()))
                            studentModel.STU_NAME = dr["姓名"].ToString();
                        else
                        {
                            Response.Write("<script>alert('第" + (count+1)+ "行出现错误,姓名不允许为空，请检查后再次导入')</script>");
                            return;
                        }
                        if (!string.IsNullOrEmpty(dr["用户名"].ToString()))
                        {
                            if (studentsService.GetModelByUserName(dr["用户名"].ToString()) != null)
                            {
                                Response.Write("<script>alert('第" + (count + 1) + "行出现错误,该用户名已经存在，请更改用户名')</script>");
                                return;
                            }
                            else
                                studentModel.STU_USERNAME = dr["用户名"].ToString();
                        }
                        else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,用户名不允许为空，请检查后再次导入')</script>");
                            return;
                        }
                        if (!string.IsNullOrEmpty(dr["密码"].ToString()))
                        {
                            studentModel.STU_PASSWORD = dr["密码"].ToString();
                        }
                        else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,密码不允许为空，请检查后再次导入')</script>");
                            return;
                        }
                        //if (!string.IsNullOrEmpty(dr["身份证号"].ToString()))
                        //{
                        //    studentModel.STU_IDNUMBER = dr["身份证号"].ToString();
                        //}
                        //else
                        //{
                        //    studentModel.STU_IDNUMBER = "";
                        //}

                        if (dr["性别"].ToString().Trim() =="男")
                        {
                            studentModel.STU_GENDER = "1";
                        }
                        else if (dr["性别"].ToString().Trim() == "女")
                        {
                            studentModel.STU_GENDER = "0";
                        }
                        else
                        {
                            studentModel.STU_GENDER = "";
                        }
                        if (!string.IsNullOrEmpty(dr["班级编号"].ToString()))
                        {
                            string classId = classes.GetIdByCode(dr["班级编号"].ToString());
                            if (!string.IsNullOrEmpty(classId))
                                studentModel.STU_CLASSES = classId;
                            else
                            {
                                Response.Write("<script>alert('第" + (count + 1) + "行出现错误,班级中不包含此班级编号，请检查后再次导入')</script>");
                                return;
                            }
                        }
                        else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,班级编号不允许为空，请检查后再次导入')</script>");
                            return;
                        }
                        if (dr["年级"] != null && !string.IsNullOrEmpty(dr["年级"].ToString()))
                        {
                            studentModel.STU_GRADE = dr["年级"].ToString();
                        }
                        else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,年级不允许为空,请检查后再次导入')</script>");
                            return;
                        }
                        studentModel.STU_IsShow = true;
                        studentsService.CreateStudent(studentModel);
                            count++;
                    }
                    Response.Write("<script>alert('添加成功，共添加" + (count-1).ToString() + "条数据')</script>");

                }
                else
                    //当然这个工作可以在前台做，可以用javascript来做，给upfileload添加一个事件即可
                    Response.Write("<script>alert('请选择正确的excel表格')</script>");
            }
        }
    }
}